package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import orm.ChildrenBehaviorPurpose;
import orm.CollectivityWeekTimeBucket;
import util.DBHelper;

public class CWeekTimeBucketDAO {
	
	public void insertCWeekTimeBucket(Statement statement, 
			ArrayList<CollectivityWeekTimeBucket> cwtbArr, String cWeekArrangeID) throws SQLException {
		CollectivityWeekTimeBucket cwtb = null;
		for (int i = 0; i < cwtbArr.size(); i++) {
			cwtb = cwtbArr.get(i);
			statement.execute("insert into collectivityweektimebucket values('" + cWeekArrangeID + "'," +
					cwtb.getWeekID() + ", " + cwtb.getTimeBucketID() + ", '" + cwtb.getThemeActivity() +
					"', '" + cwtb.getActivityArea() + "', '" + cwtb.getOpenAir() + "')");
		}
	}
	
	public void updateCWeekTimeBucket(Statement statement,
			ArrayList<CollectivityWeekTimeBucket> cwtbArr, String cWeekArrangeID) throws SQLException {
		CollectivityWeekTimeBucket cwtb = null;
		for (int i = 0; i < cwtbArr.size(); i++) {
			cwtb = cwtbArr.get(i);
			StringBuffer sqlStr = new StringBuffer();
			sqlStr.append("update collectivityweektimebucket set ThemeActivity = '");
			sqlStr.append(cwtb.getThemeActivity() + "', ActivityArea = '" + cwtb.getActivityArea());
			sqlStr.append("', OpenAir = '" + cwtb.getOpenAir() + "' where CWeekArrangeID = '");
			sqlStr.append(cwtb.getCWeekArrangeID() + "' and WeekID = " + cwtb.getWeekID() + " and TimeBucketID = " + cwtb.getTimeBucketID() + "");
			
			statement.execute(sqlStr.toString());
		}
	}
	
	public void deleteCWeekTimeBucket(Statement statement, String cWeekArrangeID) throws SQLException {
		statement.execute("delete from collectivityweektimebucket where CWeekArrangeID='" + cWeekArrangeID + "'");
	}
	
	public ArrayList<CollectivityWeekTimeBucket> getCWTBByCWAID(String cWeekArrangeID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		ArrayList<CollectivityWeekTimeBucket> al = new ArrayList<CollectivityWeekTimeBucket>();
		CollectivityWeekTimeBucket cwtb = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from collectivityweektimebucket where CWeekArrangeID='" + cWeekArrangeID + "'");
		
		while (rs.next()) {
			cwtb = new CollectivityWeekTimeBucket();
			int i = 1;
			cwtb.setCWeekArrangeID(rs.getString(i++));
			cwtb.setWeekID(rs.getInt(i++));
			cwtb.setTimeBucketID(rs.getInt(i++));
			cwtb.setThemeActivity(rs.getString(i++));
			cwtb.setActivityArea(rs.getString(i++));
			cwtb.setOpenAir(rs.getString(i++));
			al.add(cwtb);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
}
